package com.ssyt.yt1.day25;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.Date;

import com.ssyt.yt1.common.util.DBUtil;
import com.ssyt.yt1.common.util.Util;

/**
 * CRUD
 *
 * @author Hsu.
 * @since 2015年12月28日
 */
public class CRUDDemo {

	/**
	 * 增、删、改
	 * 
	 * @throws SQLException
	 * @throws NullPointerException
	 */
	public static void test1(User user) throws SQLException {
		if (user == null) {
			throw new NullPointerException("传入的对象不能为空！");
		}

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			// 3、操作数据库
			connection = DBUtil.getConnection();
			String sql = "insert into user values(null, ?, ?, ?, ?, ?, ?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user.getIdcard());
			preparedStatement.setString(2, user.getUsername());
			preparedStatement.setString(3, user.getPassword());
			preparedStatement.setBigDecimal(4, user.getSalary());
			preparedStatement.setTimestamp(5, new Timestamp(user.getBirthday()
					.getTime()));
			preparedStatement.setString(6, user.getMemo());
			int row = preparedStatement.executeUpdate();
			System.out.println("受影响的行数：" + row);
		} finally {
			// 4、关闭资源
			DBUtil.close(connection, preparedStatement);
		}
	}

	/**
	 * 查询
	 * 
	 * @throws SQLException
	 */
	public static void test2() throws SQLException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			// 3、操作数据库
			connection = DBUtil.getConnection();
			String sql = "select * from user";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String dateStr = null;
				// getDate() -> 年月日
				// getTime() -> 时分秒
				// getTimestamp() -> 年月日时分秒毫秒
				Date date = resultSet.getTimestamp("birthday");
				if (date != null) {
					dateStr = Util.format(date, "yyyy-MM-dd HH:mm:ss");
				}

				System.out.println(resultSet.getInt("id") + ", "
						+ resultSet.getString("idcard") + ", "
						+ resultSet.getString("username") + ", "
						+ resultSet.getString("password") + ", "
						+ resultSet.getBigDecimal("salary") + ", " + dateStr
						+ ", " + resultSet.getString("memo"));
			}
		} finally {
			// 4、关闭资源
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 查询总记录
	 * 
	 * @throws SQLException
	 */
	public static void test3() throws SQLException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			// 3、操作数据库
			connection = DBUtil.getConnection();
			String sql = "select count(*) count from user where birthday is not null";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				// System.out.println(resultSet.getLong(1));
				System.out.println(resultSet.getLong("count"));
			}
		} finally {
			// 4、关闭资源
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 多表查询:查看姓名为张三同学念过哪些学校？
	 * 
	 * @throws SQLException
	 */
	public static void test4() throws SQLException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			// 3、操作数据库
			connection = DBUtil.getConnection();
			String sql = "select u.id as user_id, u.username, s.id as school_id, s.school_name from user u, school s where u.id=s.user_id and u.username=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, "张三");
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				System.out.println(resultSet.getInt("user_id") + "\t"
						+ resultSet.getString("username") + "\t"
						+ resultSet.getInt("school_id") + "\t"
						+ resultSet.getString("school_name"));
			}
		} finally {
			// 4、关闭资源
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException, ParseException {
		// User user = new User();
		// user.setIdcard("123");
		// user.setUsername("张三");
		// user.setPassword("admin");
		// user.setSalary(new BigDecimal("999.90"));
		// user.setBirthday(Util.parse("1995-12-11", "yyyy-MM-dd"));
		// user.setMemo(".........");
		// test1(user);

		test2();

		// test3();

		// test4();
	}
}
